package cn.edu.imut.dao;

import cn.edu.imut.bean.CartBean;
import cn.edu.imut.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class CartDao {

    /**
     * 根据用户ID获取购物车中的商品
     */
    public List<CartBean> getCartItemsByUserId(int userId) {
        List<CartBean> cartItems = new ArrayList<>();
        String sql = "SELECT * FROM gs_cart WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                CartBean cartItem = new CartBean(
                        rs.getInt("user_id"),
                        rs.getInt("product_id"),
                        rs.getInt("product_quantity"),
                        rs.getString("product_name"),
                        rs.getInt("product_category"),
                        rs.getDouble("total_price"),
                        rs.getTimestamp("added_time").toLocalDateTime()
                );
                cartItems.add(cartItem);
            }

        } catch (SQLException e) {
            System.err.println("从数据库获取购物车商品失败：" + e.getMessage());
        }
        return cartItems;
    }

    /**
     * 添加商品到购物车
     */
    public void addToCart(CartBean cartItem) {
        String sql = "INSERT INTO gs_cart (user_id, product_id, product_quantity, product_name, product_category, total_price, added_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?) " +
                "ON DUPLICATE KEY UPDATE product_quantity = product_quantity + VALUES(product_quantity), " +
                "total_price = total_price + VALUES(total_price)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, cartItem.getUser_Id());
            pstmt.setInt(2, cartItem.getProduct_Id());
            pstmt.setInt(3, cartItem.getProduct_quantity());
            pstmt.setString(4, cartItem.getProduct_name());
            pstmt.setInt(5, cartItem.getProduct_category());
            pstmt.setDouble(6, cartItem.getTotal_price());
            pstmt.setTimestamp(7, java.sql.Timestamp.valueOf(cartItem.getAddedTime()));
            pstmt.executeUpdate();

            System.out.println("商品已成功添加到购物车！");
        } catch (SQLException e) {
            System.err.println("添加商品到购物车失败：" + e.getMessage());
        }
    }

    /**
     * 移除购物车中的商品
     */
    public void removeFromCart(int userId, int productId) {
        String sql = "DELETE FROM gs_cart WHERE user_id = ? AND product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("商品已从购物车中移除！");
            } else {
                System.out.println("购物车中未找到指定商品！");
            }
        } catch (SQLException e) {
            System.err.println("移除购物车商品失败：" + e.getMessage());
        }
    }

    /**
     * 清空购物车
     */
    public void clearCartByUserId(int userId) {
        String sql = "DELETE FROM gs_cart WHERE user_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("购物车已清空！");
            } else {
                System.out.println("购物车本来就是空的！");
            }
        } catch (SQLException e) {
            System.err.println("清空购物车失败：" + e.getMessage());
        }
    }

    /**
     * 根据用户ID获取购物车中的商品
     */
    public List<CartBean> getCartByUserId(final int user_id) {
        List<CartBean> cartItems = new ArrayList<>();
        String sql = "SELECT * FROM gs_cart WHERE user_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, user_id);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                CartBean cartItem = new CartBean(
                        rs.getInt("user_id"),
                        rs.getInt("product_id"),
                        rs.getInt("product_quantity"),
                        rs.getString("product_name"),
                        rs.getInt("product_category"),
                        rs.getDouble("total_price"),
                        rs.getTimestamp("added_time").toLocalDateTime()
                );
                cartItems.add(cartItem);
            }

        } catch (SQLException e) {
            System.err.println("从数据库获取购物车商品失败：" + e.getMessage());
        }

        return cartItems;
    }

    /**
     * 更新购物车商品的数量和总价格
     */
    public boolean updateProductQuantity(final int user_id, final int product_id, final int product_quantity, final Double total_price) {
        String sql = "UPDATE gs_cart SET product_quantity = ?, total_price = ? WHERE user_id = ? AND product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, product_quantity);
            pstmt.setDouble(2, total_price);
            pstmt.setInt(3, user_id);
            pstmt.setInt(4, product_id);

            int rowsUpdated = pstmt.executeUpdate();
            return rowsUpdated > 0;
        } catch (SQLException e) {
            System.err.println("更新购物车商品数量失败：" + e.getMessage());
            return false;
        }
    }

    /**
     * 根据用户ID和商品ID删除购物车商品
     */
    public boolean deleteCartByUserIdAndProductId(final int user_id, final int product_id) {
        String sql = "DELETE FROM gs_cart WHERE user_id = ? AND product_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, user_id);
            pstmt.setInt(2, product_id);

            int rowsDeleted = pstmt.executeUpdate();
            return rowsDeleted > 0;
        } catch (SQLException e) {
            System.err.println("删除购物车商品失败：" + e.getMessage());
            return false;
        }
    }

    /**
     * 添加商品到购物车
     */
    public void addCart(final CartBean cartBean) {
        String sql = "INSERT INTO gs_cart (user_id, product_id, product_quantity, product_name, product_category, total_price, added_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, cartBean.getUser_Id());
            pstmt.setInt(2, cartBean.getProduct_Id());
            pstmt.setInt(3, cartBean.getProduct_quantity());
            pstmt.setString(4, cartBean.getProduct_name());
            pstmt.setInt(5, cartBean.getProduct_category());
            pstmt.setDouble(6, cartBean.getTotal_price());
            pstmt.setObject(7, cartBean.getAddedTime()); // 使用 setObject 处理 LocalDateTime

            pstmt.executeUpdate();
            System.out.println("商品已添加到购物车：" + cartBean);
        } catch (SQLException e) {
            System.err.println("添加商品到购物车失败：" + e.getMessage());
        }
    }
}
